from flask import Flask, request, jsonify
import threading
from src.service.data_service import DataService
from src.service.connection_manager import ConnectionManager


class HospitalAPI:
    def __init__(self, user: str, password: str):
        self.user = user
        self.password = password
        self.conn_mgr = None
        self.data_svc = None
        self.app = Flask(__name__)
        self._setup_routes()

    def _init_services(self):
        """Initialize database service"""
        try:
            self.conn_mgr = ConnectionManager()
            conn, dbname = self.conn_mgr.login(self.user, self.password)
            self.data_svc = DataService(conn)
            print(f"API: Successfully connected to the database '{dbname}'")
            return True
        except Exception as e:
            print(f"API: Database connection failed: {e}")
            return False

    def _parse_form_data(self):
        """Parse form data"""
        data = {}
        for key, value in request.form.items():
            if value and value not in ['', 'null', 'undefined']:
                if value.lower() == 'true':
                    data[key] = True
                elif value.lower() == 'false':
                    data[key] = False
                else:
                    try:
                        if '.' in value:
                            data[key] = float(value)
                        else:
                            data[key] = int(value)
                    except ValueError:
                        data[key] = value
        return data

    def _parse_query_params(self):
        """Parse query parameters"""
        params = {}
        for key, value in request.args.items():
            if value and value not in ['', 'null', 'undefined']:
                if value.lower() == 'true':
                    params[key] = True
                elif value.lower() == 'false':
                    params[key] = False
                else:
                    try:
                        if '.' in value:
                            params[key] = float(value)
                        else:
                            params[key] = int(value)
                    except ValueError:
                        params[key] = value
        return params

    def _setup_routes(self):
        """Set API routing"""

        @self.app.route('/api/health', methods=['GET'])
        def health_check():
            """Health check endpoint"""
            return jsonify({
                'status': 'healthy',
                'service': 'Hospital Management System API',
                'version': '1.0'
            })

        # ========== Universal CRUD operation ==========

        @self.app.route('/api/query/<path:table>', methods=['GET'])
        def query_table(table):
            """Query table data"""
            try:
                where_dict = self._parse_query_params()
                columns, rows = self.data_svc.query(table, where_dict)
                result = {
                    'table': table,
                    'columns': columns,
                    'data': [dict(zip(columns, row)) for row in rows],
                    'count': len(rows)
                }
                return jsonify(result)
            except Exception as e:
                return jsonify({'error': str(e)}), 500

        @self.app.route('/api/insert/<path:table>', methods=['POST'])
        def insert_table(table):
            """Insert a new record"""
            try:
                value_dict = self._parse_form_data()

                if not value_dict:
                    return jsonify({'error': 'No data provided'}), 400

                row_count = self.data_svc.insert(table, value_dict)
                return jsonify({
                    'affected_rows': row_count,
                    'message': 'Insert successful',
                    'table': table
                })
            except Exception as e:
                return jsonify({'error': str(e)}), 500

        @self.app.route('/api/update/<path:table>', methods=['PUT'])
        def update_table(table):
            """update record"""
            try:
                pk_dict = self._parse_query_params()

                if not pk_dict:
                    return jsonify({'error': 'Primary key conditions required in query parameters'}), 400

                value_dict = self._parse_form_data()

                if not value_dict:
                    return jsonify({'error': 'No update data provided'}), 400

                row_count = self.data_svc.update(table, value_dict, pk_dict)
                return jsonify({
                    'affected_rows': row_count,
                    'message': 'Update successful',
                    'table': table
                })
            except Exception as e:
                return jsonify({'error': str(e)}), 500

        @self.app.route('/api/delete/<path:table>', methods=['DELETE'])
        def delete_table(table):
            """delete record"""
            try:
                pk_dict = self._parse_query_params()

                if not pk_dict:
                    return jsonify({'error': 'Primary key conditions required in query parameters'}), 400

                row_count = self.data_svc.delete(table, pk_dict)
                return jsonify({
                    'affected_rows': row_count,
                    'message': 'Delete successful',
                    'table': table
                })
            except Exception as e:
                return jsonify({'error': str(e)}), 500

        # ========== Complex query ==========

        @self.app.route('/api/complex-query/<query_name>', methods=['GET'])
        def execute_complex_query(query_name):
            """Execute predefined complex queries"""
            try:
                params = self._parse_query_params()

                if 'name_pattern' in params and isinstance(params['name_pattern'], str):
                    params['name_pattern'] = f'%{params["name_pattern"]}%'

                columns, rows = self.data_svc.execute_complex_query(query_name, params)
                result = {
                    'query': query_name,
                    'columns': columns,
                    'data': [dict(zip(columns, row)) for row in rows],
                    'count': len(rows)
                }
                return jsonify(result)
            except Exception as e:
                return jsonify({'error': str(e)}), 500

        # ========== Metadata query ==========

        @self.app.route('/api/columns/<path:table>', methods=['GET'])
        def get_columns(table):
            """Get table structure information"""
            try:
                columns = self.data_svc.get_columns(table)
                result = [{
                    'name': col[0],
                    'type': col[1],
                    'is_serial': bool(col[2])
                } for col in columns]
                return jsonify({
                    'table': table,
                    'columns': result
                })
            except Exception as e:
                return jsonify({'error': str(e)}), 500

        @self.app.route('/api/primary-keys/<path:table>', methods=['GET'])
        def get_primary_keys(table):
            """Get the primary key information of the table"""
            try:
                pks = self.data_svc.get_primary_keys(table)
                return jsonify({
                    'table': table,
                    'primary_keys': pks
                })
            except Exception as e:
                return jsonify({'error': str(e)}), 500

    def start(self, host='localhost', port=5000):
        """Start API service"""
        if not self._init_services():
            print("API: Service startup failed.")
            return False

        def run_flask():
            print(f"API: Start the service at http://{host}:{port}")
            print("Available endpoints:")
            print("  GET  /api/health")
            print("  GET  /api/query/<table>")
            print("  POST /api/insert/<table>")
            print("  PUT  /api/update/<table>")
            print("  DELETE /api/delete/<table>")
            print("  GET  /api/complex-query/<query_name>")
            print("  GET  /api/columns/<table>")
            print("  GET  /api/primary-keys/<table>")

            self.app.run(host=host, port=port, debug=False, use_reloader=False)

        api_thread = threading.Thread(target=run_flask, daemon=True)
        api_thread.start()
        return True